import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from sqlalchemy import create_engine
import matplotlib.cm as cm


def fetch_trading_data():
    """从数据库获取交易数据"""
    # 创建数据库连接
    db_url = "mysql+pymysql://root:yourpassword@localhost:3306/salessvc?charset=utf8mb4"
    engine = create_engine(db_url, pool_pre_ping=True)

    try:
        # 查询交易数据和交易员信息
        query = """
        SELECT 
            b.created_by,
            b.bid_time AS created_time,
            a.bond_code
        FROM 
            auction_bids b
        JOIN 
            auction_assets a ON b.auction_id = a.id
        WHERE 
            b.bid_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH)  # 获取最近3个月数据
        """
        df = pd.read_sql(query, engine)
        return df
    except Exception as e:
        print(f"数据库查询错误: {e}")
        return pd.DataFrame()
    finally:
        engine.dispose()


def analyze_trader_activity(df):
    """分析交易员活动模式"""
    if df.empty:
        print("没有可用的交易数据")
        return

    # 数据预处理
    df_weekday = df[['created_by', 'created_time']].copy()
    df_weekday['created_time'] = pd.to_datetime(df_weekday['created_time'])
    df_weekday['weekday'] = df_weekday.created_time.dt.dayofweek + 1  # 1=Monday, 7=Sunday

    # 按交易员和星期几分组统计
    x_var = 'created_by'
    groupby_var = 'weekday'
    df_weekday_agg = df_weekday.loc[:, [x_var, groupby_var]].groupby(groupby_var)
    vals = [d[x_var].value_counts().tolist() for i, d in df_weekday_agg]
    traders = df_weekday[x_var].unique().tolist()

    # 绘制交易员活动直方图
    plt.figure(figsize=(16, 9), dpi=80)
    colors = [cm.Spectral(i / float(len(vals) - 1)) for i in range(len(vals))]

    # 创建堆叠直方图
    bottom = np.zeros(len(traders))
    for i, (val_list, color) in enumerate(zip(vals, colors)):
        plt.bar(traders, val_list, bottom=bottom, color=color,
                label=f'{["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"][i]}')
        bottom += np.array(val_list)

    # 图表装饰
    plt.title("Trader Activities by Weekday (Last 3 Months)", fontsize=22)
    plt.xlabel("Trader ID", fontsize=16)
    plt.ylabel("Number of Bids", fontsize=16)
    plt.xticks(rotation=45, ha='right', fontsize=12)
    plt.legend(title='Weekday', bbox_to_anchor=(1.05, 1), loc='upper left')
    plt.grid(axis='y', alpha=0.3)
    plt.tight_layout()
    plt.show()

    # 返回分析结果
    activity_stats = {
        'total_trades': len(df),
        'unique_traders': len(traders),
        'most_active_day': df_weekday['weekday'].value_counts().idxmax(),
        'most_active_trader': df_weekday['created_by'].value_counts().idxmax()
    }

    return activity_stats


def get_bond_statistics():
    """获取债券市场统计信息"""
    db_url = "mysql+pymysql://root:yourpassword@localhost:3306/salessvc?charset=utf8mb4"
    engine = create_engine(db_url, pool_pre_ping=True)

    try:
        with engine.connect() as conn:
            # 债券统计查询
            result = conn.execute("""
                SELECT 
                    a.bond_code, 
                    COUNT(b.id) as bid_count,
                    a.issuer
                FROM 
                    auction_assets a 
                LEFT JOIN 
                    auction_bids b ON a.id = b.auction_id 
                GROUP BY 
                    a.bond_code, a.issuer
                ORDER BY 
                    bid_count DESC
                LIMIT 10
            """)

            bond_stats = []
            for row in result:
                bond_stats.append({
                    'bond_code': row['bond_code'],
                    'bid_count': row['bid_count'],
                    'issuer': row['issuer']
                })

            return bond_stats
    except Exception as e:
        print(f"数据库操作错误: {e}")
        return []
    finally:
        engine.dispose()


# 主程序
if __name__ == "__main__":
    # 1. 获取交易数据
    trading_data = fetch_trading_data()

    if not trading_data.empty:
        # 2. 分析交易员活动模式
        activity_stats = analyze_trader_activity(trading_data)
        print("\n交易员活动统计:")
        print(f"总交易数: {activity_stats['total_trades']}")
        print(f"唯一交易员数量: {activity_stats['unique_traders']}")
        print(
            f"最活跃交易日: {['周一', '周二', '周三', '周四', '周五', '周六', '周日'][activity_stats['most_active_day'] - 1]}")
        print(f"最活跃交易员: {activity_stats['most_active_trader']}")

        # 3. 获取债券市场统计
        top_bonds = get_bond_statistics()
        print("\n最活跃债券TOP10:")
        for i, bond in enumerate(top_bonds, 1):
            print(f"{i}. {bond['bond_code']} (发行人: {bond['issuer']}) - 投标数: {bond['bid_count']}")
    else:
        print("未能获取交易数据")